#!/usr/bin/python
# -*- coding: UTF-8 -*-
import chardet
import psycopg2
import psycopg2.extras


class PostgreSQLExecuteSql:
    """
    Python Test Library for TZQ
    """

    def __init__(self):
        pass

    def get_encoding(self, file):
        # 获取文件编码类型
        # 二进制方式读取，获取字节数据，检测类型
        with open(file, 'rb') as f:
            return chardet.detect(f.read())['encoding']

    # 输出文件所有内容
    def get_file_content1(self, file_path):
        # filePath="d:/20220711-1657.sql"
        file_encoding = self.get_encoding(file_path)
        # print(get_encoding(filePath))
        file = open(file_path, "r", encoding=file_encoding)
        # print(file.read())
        str = file.read()
        file.close()
        return str

    # 按行输出文件内容
    def get_file_content2(self, filePath):
        # filePath="d:/20220711-1657.sql"
        file_encoding = self.get_encoding(filePath)
        print(self.get_encoding(filePath))
        file = open(filePath, "r", encoding=file_encoding)
        # print(file.readline())
        str = file.readline()
        file.close()
        return str

    # for循环读取文件内容
    def get_file_content3(self, filePath):
        with open(filePath, "r", encoding=self.get_encoding(filePath)) as file:
            for item in file:
                print("file_content:" + item)

    # conn = psycopg2.connect("dbname=tzqlog_pro user=tzq password=Tzq@123456 host=127.0.0.1 port=5432")
    # 连接数据库执行脚本
    def conn_db_exec_sql(self, sql_script, dbname, user, password, host, port):
        connect_string = "dbname=" + dbname + " user=" + user + " password=" + password + " host=" + host + " port=" + port
        print(connect_string)
        # 连接到数据库
        conn = psycopg2.connect(connect_string)
        # 建立游标，用来执行数据库操作
        # 这⾥创建的是⼀个字典Cursor, 这样返回的数据, 都是字典的形式, ⽅便使⽤
        # cursor = conn.cursor()
        cursor = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
        # 读取文本文件中的内容
        file_content = self.get_file_content1(sql_script)
        # 执行SQL命令
        cursor.execute(file_content)
        # 提交SQL命令
        conn.commit()
        # 执行SQL SELECT命令
        # command = 'SELECT * FROM test_conn '
        # cursor.execute(command)
        ## 获取SELECT返回的元组
        # rows = cursor.fetchall()
        # 关闭数据库连接
        conn.close()
        # print(rows)

    # 连接数据库执行脚本 - TZQLOG PRO 环境
    def exec_sql__TZQLOG_DB_PRO(self, sql_script):
        # 配置项
        dbname = "plan_prac1_db"
        user = "plan"
        password = "Tzq@12345"
        host = "127.0.0.1"
        port = "8000"
        self.conn_db_exec_sql(sql_script, dbname, user, password, host, port)


# if __name__ == '__main__':
    postgreSQLExecuteSql = PostgreSQLExecuteSql()
    # 执行的脚本文件全路径
    sql_file_path = "D:/20220706-1736-tzqlog_XXX.sql"
    # 在 TZQLOG PRO 执行脚本
    postgreSQLExecuteSql.exec_sql__TZQLOG_DB_PRO(sql_file_path)
